<?php

class UserTable extends Doctrine_Table {

    const STATUS_ACTIVE = 1;
    const STATUS_VACATION = 2;
    const STATUS_QUIT = 3;
    const ACTIVE = 1;
    const PASSIVE = 2;
    const TYPE_SERVICE = 1;
    const TYPE_SALES = 2;

    public static function getInstance() {
        return Doctrine_Core::getTable('User');
    }

    public static function getUserTypes() {
        return array(
            self::TYPE_SERVICE => 'Гоо сайханч',
            self::TYPE_SALES => 'Зөвлөх',
        );
    }

    /**
     * just here for DB connection.
     * Can be moved to any table.
     * @return type
     */
    public static function getMysqlNow() {
        $pdo = Doctrine_Manager::connection()->getDbh();
        if ($_SERVER['SERVER_ADDR'] == '127.0.0.1') {
            $row = $pdo->query("SELECT NOW() AS 'datetime'")->fetchAll();
        } else {
            $sql = "SELECT " . AppTools::convertTimezone("NOW()") . " AS 'datetime'";
            $row = $pdo->query($sql)->fetchAll();
        }
        return $row[0]['datetime'];
    }

    public static function retrieveByStatement($values) {
        $query = self::getInstance()->createQuery();
        foreach ($values AS $index => $value) {
            $query->addWhere("$index = ?", $value);
        }
        return $query->fetchOne();
    }

    public static function retrieveByPk($id) {
        return self::getInstance()->findOneBy('id', $id);
    }

    public static function retrieveByUsername($username) {
        return self::getInstance()
                        ->createQuery('a')
                        ->where('a.username = ?', $username)
                        ->limit(1)
                        ->fetchOne();
    }

    public static function getFullname($id) {
        $obj = self::getInstance()
                ->createQuery('a')
                ->where('a.id = ?', $id)
                ->limit(1)
                ->fetchOne();

        return $obj ? $obj->getUsername() : '';
    }

    public static function getForSelect($storeId = 0, $type = 0) {
        $storeId = (int) $storeId;
        $type = (int) $type;
        $where = array();
        $where[] = "u.is_active = " . self::ACTIVE;

        if ($storeId) {
            $where[] = "u.store_id = $storeId";
        }
        if ($type) {
            $where[] = "u.type = $type";
        }
        $sql = "SELECT 
                    u.id,
                    u.username,
                    u.firstname,
                    u.lastname
                FROM user AS u
                WHERE " . implode(' AND ', $where) . "
                ORDER BY u.username ASC";

        $pdo = Doctrine_Manager::connection()->getDbh();
        $rows = $pdo->query($sql)->fetchAll();

        $arr = array();
        foreach ($rows AS $row) {
            $arr[$row['id']] = $row['username'] . ' | ' . mb_substr($row['lastname'], 0, 2, 'utf-8') . '.' . $row['firstname'];
        }

        return $arr;
    }

    public static function getList($storeId = 0, $isActive = 0, $name = null, $type = 0) {
        $storeId = (int) $storeId;
        $isActive = (int) $isActive;
        $type = (int) $type;
        $where = array();

        if ($isActive) {
            $where[] = "u.is_active = $isActive";
        }
        if ($name) {
            $where[] = "u.firstname LIKE '%" . $name . "%' OR u.lastname LIKE '%" . $name . "%' ";
        }
        if ($storeId) {
            $where[] = "u.store_id = $storeId";
        }
        if ($type) {
            $where[] = "u.type = $type";
        }
        $where = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';


        $sql = "SELECT 
                    u.id,
                    u.username,
                    u.firstname,
                    u.lastname,
                    u.created_at,
                    u.is_active,
                    u.type,
                    s.id AS store_id,
                    s.name AS store_name
                FROM `user` u
                LEFT JOIN `store` s ON s.id = u.store_id
                $where
                ORDER BY u.username ASC";

        $pdo = Doctrine_Manager::connection()->getDbh();
        $rows = $pdo->query($sql)->fetchAll();
        return $rows;
    }

    public static function getListForSync($type = 0, $dateTimeBegin = null) {
        $type = (int) $type;
        $where = array();

        if ($type) {
            $where[] = "u.type = $type";
        }
        if ($dateTimeBegin) {
            $where[] = AppTools::convertTimezone('u.updated_at') . " >= '$dateTimeBegin'";
        }
        $where = count($where) ? 'WHERE ' . implode(' AND ', $where) : '';

        $sql = "SELECT 
                    u.id,
                    u.username,
                    u.firstname,
                    u.lastname,
                    u.is_active,
                    u.type,
                    s.id AS store_id,
                    s.name AS store_name
                FROM `user` u
                LEFT JOIN `store` s ON s.id = u.store_id
                $where
                ORDER BY u.username ASC";

        $pdo = Doctrine_Manager::connection()->getDbh();
        $rows = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        return $rows;
    }

    public static function checkAuth($userId, $authkey) {
        $userId = (int) $userId;
        if (!$userId || !$authkey) {
            return false;
        }

        $obj = self::getInstance()
                ->createQuery()
                ->where('id = ?', $userId)
                ->addWhere('auth_key = ?', $authkey)
                ->fetchOne();

        if ($obj) {
            return true;
        }

        return false;
    }

    public static function renewAuth($userId) {
        $user = self::retrieveByPk($userId);
        if ($user) {
            $authKey = md5(rand(0, 1000) . 'bojss');
            $user['auth_key'] = $authKey;
            $user->save();
            return $authKey;
        }
        return false;
    }

}
